﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web;

/// <summary>
/// Sql Connection
/// </summary>
public class SqlConn : IDisposable
{
  /// <summary>
  /// 
  /// </summary>
  private SqlConnection conn = null;

  /// <summary>
  /// 
  /// </summary>
  public SqlConn()
  {

  }

  /// <summary>
  /// 
  /// </summary>
  /// <param name="lang"></param>
  /// <returns></returns>
  private string SqlLanguage(string lang)
  {
    try
    {
      switch (lang.ToLower())
      {
        case "hu-hu":
          return "Hungarian";

        case "de-de":
          return "German";
        
        case "en-uk":
          return "British";

        default:
        case "en-us":
          return "English";
      }
    }
    catch 
    {
      return "English";
    }
  }

  /// <summary>
  /// Open connection
  /// </summary>
  /// <param name="connectionString"></param>
  public void Open(string connectionString, string lang = null)
  {
    if (conn == null)
    {
      conn = new SqlConnection(connectionString + ";Current Language=" + SqlLanguage(lang));
      conn.Open();
    }
  }

  /// <summary>
  /// Close connection
  /// </summary>
  public void Close()
  {
    if (conn != null)
    {
      conn.Close();
      conn = null;
    }
  }

  /// <summary>
  /// Execute SQL command
  /// </summary>
  /// <param name="sqltext"></param>
  /// <param name="parameters"></param>
  /// <param name="tran"></param>
  /// <returns></returns>
  public DataTable Execute(string sqltext, Array parameters, bool transaction = false)
  {
    DataTable table = new DataTable();

    using (SqlCommand cmd = new SqlCommand(sqltext, conn))
    {
      try
      {
        // Parameters
        cmd.Parameters.AddRange(parameters);

        // maybe...
        if (transaction == true)
        {
          cmd.Transaction = cmd.Connection.BeginTransaction();
        }

        // Execute command and get result
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
          da.Fill(table);
        }

        // OK
        if (transaction == true)
          cmd.Transaction.Commit();

        return table;
      }
      catch (Exception ex)
      {
        try
        {
          // FAILED
          if (transaction == true)
            cmd.Transaction.Rollback();        
        }
        catch { }

        throw ex;
      }
    }
  }


  /// <summary>
  /// 
  /// </summary>
  public void Dispose()
  {
    Close();
  }
}
